﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Collections.Specialized;
using System.Collections;
using System.Threading;

public class MysqlHelper
{
    public const string MysqlPassword = "199296zhl";

    //数据库连接字符串 
    public static string ConnectionString(string databaseName) => $"Database='{databaseName}';Data Source='127.0.0.1';User Id='root';Password='{MysqlPassword}';charset='utf8';pooling=true ;Allow User Variables=True;"; //XXX的为修改项

    static object baton = new object();
        
    /// <summary>
    /// 连接池
    /// </summary>
    private static Dictionary<string,Stack<MySqlConnection>> pool = new Dictionary<string, Stack<MySqlConnection>>();
    /// <summary>
    /// 最大连接数
    /// </summary>
    private const int POOL_MAX_SIZE = 20;
    /// <summary>
    /// 当前连接Size
    /// </summary>
    private static int m_CurrentSize = 0;
    
    static MySqlConnection GetConnection(string database)
    {
        if (!pool.ContainsKey(database))
            pool[database] = new Stack<MySqlConnection>();

        MySqlConnection conn;
        //lock (baton)
        {
            if (pool[database].Count == 0)
            {
                {
                    conn = NewConnection(database);
                    m_CurrentSize++;

                    pool[database].Push(conn);
                }
                if (m_CurrentSize >= POOL_MAX_SIZE)
                {
                    GameDebug.LogError($"连接数已达最大  m_CurrentSize:{m_CurrentSize}");
                    //try
                    //{
                    //    GameDebug.LogError($"连接数已达最大，thread:{Environment.CurrentManagedThreadId} 等待");
                    //    //Monitor.Wait(baton);
                    //}
                    //catch (Exception e)
                    //{
                    //    GameDebug.LogError(e.Message+"\n"+e.StackTrace);
                    //}
                }
            }
            
            conn = (MySqlConnection)(pool[database].Pop());
#if SomeLog
            GameDebug.LogShowy($"database：{database} thread:{Environment.CurrentManagedThreadId} 使用连接：{conn.GetHashCode()}  State:{conn.State}  m_CurrentSize:{m_CurrentSize}");
#endif

            if(conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (conn.State == ConnectionState.Closed)
            {
                GameDebug.LogError($" conn  closed");
                conn = NewConnection(database);
                conn.Open();
            }
        }
        return conn;
    }

    static void ReleaseConnection(string database,MySqlConnection conn)
    {
       // lock (baton)
        {
            if (pool.ContainsKey(database))
            {
                pool[database].Push(conn);
                conn.Close();
#if SomeLog
                Console.WriteLine($"回收连接:{conn.GetHashCode()}  pool.Count:{pool[database].Count}");
#endif
            }
            //Monitor.Pulse(baton);

        }
    }

    static MySqlConnection NewConnection(string database)
    {
        //lock (baton)
        {
            MySqlConnection newConn = new MySqlConnection(ConnectionString(database));
            newConn.Open();
            return newConn;
        }
    }

#region 公用方法
    /// <summary>
    /// 得到最大值
    /// </summary>
    /// <param name="FieldName"></param>
    /// <param name="TableName"></param>
    /// <returns></returns>
    //public static int GetMaxID(string database,string FieldName, string TableName)
    //{
    //    string strsql = "select max(" + FieldName + ")+1 from " + TableName;
    //    object obj = GetSingle(database,strsql);
    //    if (obj == null)
    //    {
    //        return 1;
    //    }
    //    else
    //    {
    //        return int.Parse(obj.ToString());
    //    }
    //}
    ///// <summary>
    ///// 是否存在
    ///// </summary>
    ///// <param name="strSql"></param>
    ///// <returns></returns>
    //public static bool Exists(string database,string strSql)
    //{
    //    object obj = GetSingle(database,strSql);
    //    int cmdresult;
    //    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    //    {
    //        cmdresult = 0;
    //    }
    //    else
    //    {
    //        cmdresult = int.Parse(obj.ToString());
    //    }
    //    if (cmdresult == 0)
    //    {
    //        return false;
    //    }
    //    else
    //    {
    //        return true;
    //    }
    //}
    /// <summary>
    /// 是否存在（基于MySqlParameter）
    /// </summary>
    /// <param name="strSql"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    //public static bool Exists(string database, string strSql, params MySqlParameter[] cmdParms)
    //{
    //    object obj = GetSingle(database,strSql, cmdParms);
    //    int cmdresult;
    //    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    //    {
    //        cmdresult = 0;
    //    }
    //    else
    //    {
    //        cmdresult = int.Parse(obj.ToString());
    //    }
    //    if (cmdresult == 0)
    //    {
    //        return false;
    //    }
    //    else
    //    {
    //        return true;
    //    }
    //}

#endregion

#region  执行简单SQL语句

    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string database,string SQLString)
    {
        lock (pool)
        {
            MySqlConnection connection = GetConnection(database);
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        // throw e;
                        GameDebug.LogError(e.Message + "\n" + e.StackTrace);
                        return -1;
                    }
                    finally
                    {
                        ReleaseConnection(database, connection);
                    }
                }
            }
        }
    }
    public static long ExecuteInsertSql(string database, string SQLString)
    {
        lock (pool)
        {
            MySqlConnection connection = GetConnection(database);
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        int rows = cmd.ExecuteNonQuery();

                        long id = cmd.LastInsertedId;
#if SomeLog
                        GameDebug.LogShowy($"插入 得到Id:{id}");
#endif
                        return id;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        GameDebug.LogError(e.Message + "\n" + e.StackTrace);
                        return -1;
                    }
                    finally
                    {
                        ReleaseConnection(database, connection);
                    }
                }
            }
        }
    }
   

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    //public static object GetSingle(string database, string SQLString)
    //{
    //    lock (pool)
    //    {
    //        MySqlConnection connection = GetConnection(database);
    //        {
    //            using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
    //            {
    //                try
    //                {
    //                    object obj = cmd.ExecuteScalar();
    //                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    //                    {
    //                        return null;
    //                    }
    //                    else
    //                    {
    //                        return obj;
    //                    }
    //                }
    //                catch (MySql.Data.MySqlClient.MySqlException e)
    //                {
    //                    GameDebug.LogError(e.Message + "\n" + e.StackTrace);
    //                    return null;
    //                }
    //                finally
    //                {
    //                    ReleaseConnection(database, connection);
    //                }
    //            }
    //        }
    //    }
    //}
    
    /// <summary>
    /// 执行查询语句，使用MySqlDataReader 回调
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>MySqlDataReader</returns>
    public static void ExecuteReader(string database, string strSQL,Action<MySqlDataReader> onEnd)
    {
        lock (pool)
        {
#if SomeLog
            GameDebug.Log($"strSQL:{strSQL}");
#endif
            MySqlConnection connection = GetConnection(database);
            MySqlCommand cmd = new MySqlCommand(strSQL, connection);
            try
            {
                MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                onEnd?.Invoke(myReader);

                myReader.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                GameDebug.LogError(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                ReleaseConnection(database, connection);
            }
        }
    }
   
    
    public static DataTable GetDataTable(string database, string SQLString)
    {
        lock (pool)
        {
            MySqlConnection connection = GetConnection(database);
            {
                DataTable ds = new DataTable();
                try
                {
                    MySqlDataAdapter adapter = new MySqlDataAdapter(SQLString, connection);
                    adapter.Fill(ds);
                }
                catch (Exception e)
                {
                    GameDebug.LogError(e.Message + "\n" + e.StackTrace);
                    return null;
                }
                finally
                {
                    ReleaseConnection(database, connection);
                }
                return ds;
            }
        }
    }

    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet GetDataSet(string SQLString)
    {
        lock (pool)
        {
            MySqlConnection connection = GetConnection("mysql");
            {
                DataSet ds = new DataSet();
                try
                {
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    GameDebug.LogError(ex.Message);
                }
                finally
                {
                    ReleaseConnection("mysql", connection);
                }
                return ds;
            }
        }
    }
    //public static DataSet GetDataSet(string SQLString, int Times)
    //{
    //    MySqlConnection connection = GetConnection("mysql");
    //    {
    //        DataSet ds = new DataSet();
    //        try
    //        {

    //            MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
    //            command.SelectCommand.CommandTimeout = Times;
    //            command.Fill(ds, "ds");
    //        }
    //        catch (MySql.Data.MySqlClient.MySqlException ex)
    //        {
    //            GameDebug.LogError(ex);
    //        }
    //        finally
    //        {
    //            ReleaseConnection("mysql",connection);
    //        }
    //        return ds;
    //    }
    //}

#endregion

#region 执行带参数的SQL语句

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="SQLString">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    //public static object GetSingle(string database, string SQLString, params MySqlParameter[] cmdParms)
    //{
    //    MySqlConnection connection = GetConnection(database);
    //    {
    //        using (MySqlCommand cmd = new MySqlCommand())
    //        {
    //            try
    //            {
    //                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
    //                object obj = cmd.ExecuteScalar();
    //                cmd.Parameters.Clear();
    //                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
    //                {
    //                    return null;
    //                }
    //                else
    //                {
    //                    return obj;
    //                }
    //            }
    //            catch (MySql.Data.MySqlClient.MySqlException e)
    //            {
    //                GameDebug.LogError(e.Message+"\n"+e.StackTrace);
    //                return null;
    //            }
    //            finally
    //            {
    //                ReleaseConnection(database,connection);
    //            }
    //        }
    //    }
    //}


    //private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
    //{
    //    if (conn.State != ConnectionState.Open)
            
    //    cmd.Connection = conn;
    //    cmd.CommandText = cmdText;
    //    if (trans != null)
    //        cmd.Transaction = trans;
    //    cmd.CommandType = CommandType.Text;//cmdType;
    //    if (cmdParms != null)
    //    {


    //        foreach (MySqlParameter parameter in cmdParms)
    //        {
    //            if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
    //                (parameter.Value == null))
    //            {
    //                parameter.Value = DBNull.Value;
    //            }
    //            cmd.Parameters.Add(parameter);
    //        }
    //    }
    //}


#endregion






}


